﻿using Npgsql;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Ortodoctor.modelo
{
    public class modeloPagamentoParcela : conexao
    {
        public modeloPagamentoParcela()
        {
            conn = new NpgsqlConnection(connectionstring);
            conn.Open();
        }

        public int insert(int id_parcela)
        {
            int id=-1;
            try
            {
                //con.open();
                String sql = "INSERT INTO pagamento_parcela(id_parcela) VALUES (:p1) returning id_pagamento_parcela";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", id_parcela);
                id = (int.Parse(com.ExecuteScalar().ToString()));
            }
            finally
            {
                conn.Close();
            }
            return id;
        }

        public int delete(int id_parcela)
        {
            int id = -1;
            try
            {
                //con.open();
                String sql = "delete from pagamento_parcela where id_parcela = :p1 returning id_pagamento_parcela";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", id_parcela);
                try
                {
                    id = (int.Parse(com.ExecuteScalar().ToString()));
                    sql = "update pagamento_parcela_hist set id_usuario_modificacao=:id_mod, data_modificacao=:data where id_parcela = :p1 returning id_pagamento_parcela";
                    com = new NpgsqlCommand(sql, conn);
                    com.Parameters.AddWithValue("p1", id_parcela);
                    com.Parameters.AddWithValue("id_mod", HttpContext.Current.Session["ID_USUARIO_ACESSO"].ToString());
                    com.Parameters.AddWithValue("data", DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss"));
                    com.ExecuteNonQuery();
                }
                catch (Exception) { };
            }
            finally
            {
                conn.Close();
            }
            return id;
        }

        public int update(int id_parcela)
        {
            int id = -1;
            try
            {
                //con.open();
                String sql = "UPDATE pagamento_parcela SET data=now(), id_usuario_modificacao = :id_mod where id_parcela = :p1 returning id_parcela";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", id_parcela);
                com.Parameters.AddWithValue("id_mod", HttpContext.Current.Session["ID_USUARIO_ACESSO"].ToString());
                try
                {
                    id = (int.Parse(com.ExecuteScalar().ToString()));
                }catch(Exception){}
            }
            finally
            {
                conn.Close();
            }
            return id;
        }

        public int exist(int id_parcela)
        {
            int id = -1;
            try
            {
                //con.open();
                String sql = "select id_pagamento_parcela from pagamento_parcela where id_parcela = :p1";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", id_parcela);
                try
                {
                    id = (int.Parse(com.ExecuteScalar().ToString()));
                }
                catch (NullReferenceException) { };
            }
            finally
            {
                conn.Close();
            }
            return id;
        }
    }


}